/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package alwv.reports.pdf;

import alwv.database.DerbyConnection;
import alwv.database.Query;
import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.Element;
import com.itextpdf.text.Font;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;
import java.awt.Desktop;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Brad
 */
public class TransactionsByCategoryAndSubcategory {
    
    private static final float LEFT = 0.25f;
    private static final float RIGHT = 0.25f;
    private static final float TOP = 50.0f;
    private static final float BOTTOM = 50.0f;
    
    public static void main(String[] args){
        DerbyConnection conn = new DerbyConnection();
        conn.createConnection();
        Calendar cal = Calendar.getInstance();
        cal.set(2011, 11, 5);
        Date date = cal.getTime();
        
        generateReport(conn.getConnection(), 2011, date, "Test.pdf");
    }
    
    public static void generateReport(Connection conn, int year, Date date, String filename) {
        try {
            SimpleDateFormat formatter = new SimpleDateFormat("MM/dd/yyyy");
            String dateString = formatter.format(date);

            Document d = new Document(PageSize.A4.rotate());
            d.setMargins(LEFT, RIGHT, TOP, BOTTOM);

            PdfWriter writer = PdfWriter.getInstance(d, new FileOutputStream(filename));
            d.open();

            Font f = new Font(Font.FontFamily.TIMES_ROMAN, 14, Font.BOLD);
            Paragraph top = new Paragraph("Transaction report as of " + dateString, f);
            top.setAlignment(Element.ALIGN_CENTER);
            d.add(top);

            PdfPTable table = new PdfPTable(10);
            float[] widths = {10f, 15f, 30f, 35f, 10f, 10f, 15f, 10f, 10f, 35f};
            //table.setLockedWidth(true);
            table.setWidths(widths);
            table.setWidthPercentage(90f);
            
            ResultSet rs = Query.getTransByCategorySubcategory(conn, year);
            
            table.setHeaderRows(1);
            
            table.addCell(header1Cell("ID"));
            table.addCell(header1Cell("Date"));
            table.addCell(header1Cell("Category"));
            table.addCell(header1Cell("Subcategory"));
            table.addCell(header1Cell("Payment Amount"));
            table.addCell(header1Cell("Check #"));
            table.addCell(header1Cell("Action"));
            table.addCell(header1Cell("Deposit Amount"));
            table.addCell(header1Cell("Associate ID"));
            table.addCell(header1Cell("Note"));
            
            while(rs.next()){
                int transID = rs.getInt(1);
                Date transDate = new Date(rs.getDate(2).getTime());
                String category = rs.getString(3);
                String subcategory = rs.getString(4);
                double paymentAmount = rs.getDouble(5);
                int checkNumber = rs.getInt(6);
                String action = rs.getString(7);
                int associateID = rs.getInt(8);
                String note = rs.getString(9);
                double depositAmount = rs.getDouble(10);
                
                table.addCell(regularCell(String.valueOf(transID)));
                table.addCell(regularCell(formatter.format(transDate)));
                table.addCell(regularCell(category));
                table.addCell(regularCell(subcategory));
                if(paymentAmount > 0.0){
                    table.addCell(regularCell(String.valueOf(paymentAmount)));
                }
                else{
                    table.addCell(regularCell(""));
                }
                if(checkNumber > -1){
                    table.addCell(regularCell(String.valueOf(checkNumber)));
                }
                else{
                    table.addCell(regularCell(""));
                }
                table.addCell(regularCell(action));
                if(depositAmount > 0.0){
                    table.addCell(regularCell(String.valueOf(depositAmount)));
                }
                else{
                    table.addCell(regularCell(""));
                }
                table.addCell(regularCell(String.valueOf(associateID)));
                table.addCell(regularCell(note));
            }
            
            d.add(table);

            d.close();

            File file = new File(filename);
            Desktop.getDesktop().open(file);
        } catch (SQLException e){
            Logger.getLogger(MonthlyReport.class.getName()).log(Level.SEVERE, null, e);
        } catch (IOException ex) {
            Logger.getLogger(MonthlyReport.class.getName()).log(Level.SEVERE, null, ex);
        } catch (DocumentException ex) {
            Logger.getLogger(MonthlyReport.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private static PdfPCell regularCell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.NORMAL);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        return cell;
    }

    private static PdfPCell regAmountCell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.NORMAL);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setHorizontalAlignment(Element.ALIGN_RIGHT);
        cell.setBorder(0);
        return cell;
    }

    private static PdfPCell overallTotalTitleCell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.BOLD);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setBorder(0);
        cell.setBorderWidthTop(0.5f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_LEFT);
        return cell;
    }

    private static PdfPCell subCategoryTotalDollarCell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.NORMAL);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setBorder(0);
        cell.setBorderWidthTop(0.5f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_RIGHT);
        return cell;
    }
    
    private static PdfPCell headingTotalDollarCell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.BOLD);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setBorder(0);
        cell.setBorderWidthTop(0.5f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_RIGHT);
        return cell;
    }

    private static PdfPCell headingCell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.BOLD);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setBorder(0);
        cell.setBorderWidthBottom(0.5f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        return cell;
    }


    private static PdfPCell header1Cell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.NORMAL);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setNoWrap(false);
        cell.setBorder(0);
        cell.setFixedHeight(28f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        return cell;
    }

    private static PdfPCell header2Cell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.NORMAL);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setNoWrap(false);
        cell.setBorder(0);
        cell.setBorderWidthBottom(0.5f);
        cell.setFixedHeight(28f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        return cell;
    }
    
}
